package com.lx.dao.impl;

import com.lx.dao.BankCardDao;
import com.lx.entity.BankCard;
import com.lx.entity.Trade;
import com.lx.entity.vo.BankCardAndUserInfo;
import com.lx.entity.vo.TradeAndCardInfoVO;
import com.lx.uitl.ConnUtil;
import com.lx.uitl.ConvertClassUtil;

import java.sql.*;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

/**
 * @title: BankCardDaoImpl
 * @projectName bankManage
 * @description: TODO
 * @author: yuanChu
 * @date: 2022/5/6  8:36
 */
public class BankCardDaoImpl implements BankCardDao {
    @Override
    public Integer addBankCard(BankCard bankCard) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        PreparedStatement ps = conn.prepareStatement("insert into bank_card(card_id,users_id,card_pwd,card_status,card_balance,open_card)values(?,?,MD5(?),?,?,?)");
        ps.setString(1,bankCard.getCardId());
        ps.setInt(2,bankCard.getUsersId());
        ps.setString(3,bankCard.getCardPwd());
        ps.setInt(4,bankCard.getCardStatus());
        ps.setDouble(5,bankCard.getCardBalance());
        ps.setDate(6, new java.sql.Date(bankCard.getOpenCard().getTime()));
        Integer i = ps.executeUpdate();
        return i;
    }
    @Override
    public int findCardCountById(String cardId) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "select count(*) from bank_card where card_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, cardId);
        //获取的结果集
        ResultSet resultSet = ps.executeQuery();
        resultSet.next();
        //获取的结果数 因为只有一行，所以直接调用         resultSet.next(); 移动指针获取返回结果
        int count = resultSet.getInt(1);
        resultSet.close();
        ConnUtil.closeConnection();
        return count;
    }

    @Override
    public BankCardAndUserInfo findCardInfoById(String cardId) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "SELECT a.*,b.user_name FROM bank_card a LEFT JOIN users b on a.users_id=b.user_id where a.card_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, cardId);
        //获取的结果集
        ResultSet resultSet = ps.executeQuery();
        List<BankCardAndUserInfo> list = new ArrayList<>();
        try {
            list = ConvertClassUtil.ConvertTo(BankCardAndUserInfo.class, resultSet);
            return list.get(0);
        } catch (InstantiationException | IllegalAccessException | ParseException e) {
            e.printStackTrace();
            System.out.println("转换错误");
            return null;
        } finally {
            resultSet.close();
            ConnUtil.closeConnection();
        }
    }

    @Override
    public List<BankCard> findCardsByUserId(Integer userId) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "SELECT * FROM bank_card where users_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, userId);
        //获取的结果集
        ResultSet resultSet = ps.executeQuery();
        List<BankCard> list = new ArrayList<>();
        try {
            list = ConvertClassUtil.ConvertTo(BankCard.class, resultSet);
            return list;
        } catch (InstantiationException | IllegalAccessException | ParseException e) {
            e.printStackTrace();
            System.out.println("转换错误");
            return null;
        } finally {
            resultSet.close();
            ConnUtil.closeConnection();
        }
    }

    @Override
    public boolean charge(String cardId, Double transMoney) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "UPDATE bank_card SET card_balance=card_balance-? where card_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setDouble(1, transMoney);
        ps.setString(2, cardId);
        //获取的结果集
        int affectRowNum = ps.executeUpdate();
        ps.close();
        if (affectRowNum > 0) {
            return true;
        }
        return false;
    }

    @Override
    public boolean addMoney(String payeeCard, Double transMoney) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "UPDATE bank_card SET card_balance=card_balance+? where card_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setDouble(1, transMoney);
        ps.setString(2, payeeCard);
        //获取的结果集
        int affectRowNum = ps.executeUpdate();
        ps.close();

        if (affectRowNum > 0) {
            return true;
        }
        return false;
    }

    @Override
    public boolean insert(Trade trade) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "INSERT into trade(card_id,trans_money,business_type,trans_time,payee_card,remark) VALUES(?,?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, trade.getCardId());
        ps.setDouble(2, trade.getTransMoney()-trade.getTransMoney()*2);
        ps.setInt(3,trade.getBusinessType());
        ps.setDate(4,new Date(trade.getTransTime().getTime()));
        ps.setString(5,trade.getPayeeCard());
        ps.setString(6,trade.getRemark());
        //获取的影响行数
        int affectRowNum = ps.executeUpdate();
        ps.close();

        if (affectRowNum > 0) {
            return true;
        }
        return false;
    }

    @Override
    public List<BankCard> findBalanceById(String cardId) throws SQLException {
        List<BankCard> list = new ArrayList<>();
        Connection conn = ConnUtil.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM bank_card where card_id =?");
        ps.setString(1,cardId);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            BankCard bankCard = new BankCard();
            bankCard.setCardId(rs.getString("card_id"));
            bankCard.setUsersId(rs.getInt("users_id"));
            bankCard.setCardPwd(rs.getString("card_pwd"));
            bankCard.setCardBalance(rs.getDouble("card_balance"));
            bankCard.setCardStatus(rs.getInt("card_status"));
            bankCard.setOpenCard(rs.getDate("open_card"));
            list.add(bankCard);
        }

        return list;
    }


    @Override
    public boolean deposit(Trade trade) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "INSERT into trade(card_id,trans_money,business_type,trans_time,remark) VALUES(?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, trade.getCardId());
        ps.setDouble(2, trade.getTransMoney());
        ps.setInt(3,trade.getBusinessType());
        ps.setDate(4,new Date(trade.getTransTime().getTime()));
        ps.setString(5,trade.getRemark());
        //获取的影响行数
        int affectRowNum = ps.executeUpdate();
        ps.close();

        if (affectRowNum > 0) {
            return true;
        }
        return false;
    }

    @Override
    public boolean withdrawal(String cardId, double withdrawal) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "UPDATE bank_card SET card_balance=card_balance-? where card_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setDouble(1, withdrawal);
        ps.setString(2, cardId);
        //获取的结果集
        int affectRowNum = ps.executeUpdate();
        ps.close();

        if (affectRowNum > 0) {
            return true;
        }
        return false;
    }

    @Override
    public Integer freeze(String cardId) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql = "UPDATE bank_card SET card_status=1 where card_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, cardId);
        int affectRowNum = ps.executeUpdate();
        ps.close();
        return affectRowNum;
    }

    @Override
    public Integer updateStatusByCardId(String cardId) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        PreparedStatement ps = conn.prepareStatement("update bank_card SET card_status = 3 where card_id = ?");
        ps.setString(1,cardId);
        int i = ps.executeUpdate();
        ps.close();
        return i;

    }

    @Override
    public List<TradeAndCardInfoVO> getAccountLastMonthRecord(Integer userId) throws SQLException {
        Connection conn = ConnUtil.getConnection();
        String sql="select bank_card.card_id,trade.trans_money,trade.trans_time,trade.business_type,trade.remark from  bank_card  LEFT JOIN trade ON bank_card.card_id=trade.card_id where users_id=? AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(trade.trans_time) order by bank_card.card_id desc,trade.trans_time";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,userId);
        ResultSet resultSet = ps.executeQuery();
        List<TradeAndCardInfoVO> list = null;
        try {
            list = ConvertClassUtil.ConvertTo(TradeAndCardInfoVO.class, resultSet);
        } catch (InstantiationException | IllegalAccessException | ParseException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询某张卡的交易信息
     *
     * @param cardNo 卡号
     * @return 查询某张卡的交易信息
     * @throws SQLException sql查询异常
     */
    @Override
    public List<TradeAndCardInfoVO> selectLastMonthRecordByCard(String cardNo) throws SQLException{
        Connection conn = ConnUtil.getConnection();
        String sql="select bank_card.card_id,trade.trans_money,trade.trans_time,trade.business_type,trade.remark from  bank_card  LEFT JOIN trade ON bank_card.card_id=trade.card_id where bank_card.card_id=? AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(trade.trans_time) order by bank_card.card_id desc,trade.trans_time";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,cardNo);
        ResultSet resultSet = ps.executeQuery();
        List<TradeAndCardInfoVO> list = null;
        try {
            list = ConvertClassUtil.ConvertTo(TradeAndCardInfoVO.class, resultSet);
        } catch (InstantiationException | IllegalAccessException | ParseException e) {
            e.printStackTrace();
        }
        return list;
    }
}
